﻿using System;
using System.Collections.Generic;
using System.Text;
using BusinessRule.Organization;
using APICommond;
using System.Data;
using System.Threading.Tasks;
using BusinessRepository;
using BusinessEntity.Xcq;

namespace BusinessRule.XCQ
{
    public class XcqU8
    {
        /// <summary>
        /// 用户登录查询
        /// </summary>
        /// <param name="username"></param>
        /// <returns></returns>
        public static async Task<DataTable> Getuser(string username, string password)
        {
            string sql = "select id from gl_czy where  name='" + username + "'and  password='" + password + "'";
            return await new RepositoryFactory().XCQRepository().FindTable(sql, null);

        }
        /// <summary>
        /// 根据用户ID，查询单位
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public static async Task<DataTable> Getdw(string userid)
        {
            string sql = @"if (select COUNT(kzdm) from GL_QXKZ where UserID='" + userid + "') <> 0";
            sql += " begin ";
            sql += " select rtrim(agency_co) as agency_co,rtrim(agency_co)+':'+rtrim(dwmc) as dmmc,dwdm from dbo.A_STdw where dwdm in (select kzdm  from GL_QXKZ QXKZ ";
            sql += " left join dbo.PubGSZL DW on rtrim(DW.GSDM)=rtrim(QXKZ.KZDM) ";
            sql += " where Kzlx='G' and UserID='" + userid + "' and dw.kjnd='" + DateTime.Today.Year + "')";
            sql += " end ";
            sql += " if (select COUNT(kzdm) from GL_QXKZ where UserID='" + userid + "') = 0";
            sql += " begin ";
            sql += "select rtrim(agency_co) as agency_co,rtrim(agency_co)+':'+rtrim(dwmc) as dmmc,dwdm from dbo.A_STdw where dwdm in ( select distinct gsdm from PubGSZL where kjnd='" + DateTime.Today.Year + "' ) end ";
            return await new RepositoryFactory().XCQRepository().FindTable(sql, null);
        }
        /// <summary>
        /// 查询支付信息
        /// </summary>
        /// <param name="gsdm"></param>
        /// <param name="startime"></param>
        /// <param name="endtime"></param>
        /// <param name="zt">zt="" 查询全部，0未传凭证，1已传凭证</param>
        /// <returns></returns>
        public static async Task<DataTable> SelecTZfInfo(string gsdm, DateTime startime, DateTime endtime, string zt)
        {
            string sql = "";
            if (zt == "")
            {
                sql = "select * from pay_voucher_bill_1_0 where AGENCY_CODE='" + gsdm + "' and to_char(xPAY_DATE,'yyyyMMdd')>='" + startime.ToString("yyyyMMdd") + "' and to_char(xPAY_DATE,'yyyyMMdd')<='" + endtime.ToString("yyyyMMdd") + "' and is_deleted='0' and xpay_date is not null";
            }
            else if (zt == "0")
            {
                sql = "select * from pay_voucher_bill_1_0 where pay_cert_id not in (select pay_cert_id from pay_pz) and AGENCY_CODE='" + gsdm + "' and to_char(xPAY_DATE,'yyyyMMdd')>='" + startime.ToString("yyyyMMdd") + "' and to_char(xPAY_DATE,'yyyyMMdd')<='" + endtime.ToString("yyyyMMdd") + "' and is_deleted='0' and xpay_date is not null";
            }
            else
            {
                sql = "select * from pay_voucher_bill_1_0 where pay_cert_id  in (select pay_cert_id from pay_pz) and AGENCY_CODE='" + gsdm + "' and to_char(xPAY_DATE,'yyyyMMdd')>='" + startime.ToString("yyyyMMdd") + "' and to_char(xPAY_DATE,'yyyyMMdd')<='" + endtime.ToString("yyyyMMdd") + "' and is_deleted='0' and xpay_date is not null";
            }
            return await new RepositoryFactory().XCQOrcaleRepository().FindTable(sql, null);
        }
        /// <summary>
        /// 取消生成的凭证1 根据支付凭证ID 删除省厅回流数据已生成数据 标志
        /// </summary>
        /// <param name="PAY_CERT_ID">支付凭证ID</param>
        /// <returns></returns>
        public static async Task<int> deleteTZfInfo(string PAY_CERT_ID)
        {
            string sql = "delete from pay_pz where PAY_CERT_ID='" + PAY_CERT_ID + "'";
            return await new RepositoryFactory().XCQOrcaleRepository().ExecuteSql(sql);
        }
        /// <summary>
        ///  取消生成的凭证2 修改U8 生成的凭证内容
        /// </summary>
        /// <param name="kjnd">会计年度</param>
        /// <param name="pdqj1">已传凭证标志</param>
        /// <param name="pdh">凭单号</param>
        /// <returns></returns>
        public static async Task<int> UpdatePZ1(string PAY_CERT_ID)
        {
            string sql = " begin transaction declare @gsdm nvarchar(50); declare @kjqj nvarchar(50); declare @pzh nvarchar(50);declare @pzly nvarchar(50);";
            sql += "  select @gsdm=gsdm,@kjqj=kjqj,@pzh=pzh,@pzly=pzly from gl_pznr where fzsm2='" + PAY_CERT_ID + "' ";
            sql += " delete from gl_pzml where gsdm=@gsdm and kjqj=@kjqj and pzh=@pzh and pzly=@pzly ";
            sql += " delete from gl_pznr where fzsm2='" + PAY_CERT_ID + "' commit transaction";


            return await new RepositoryFactory().XCQRepository().ExecuteSql(sql);
        }

        /// <summary>pay_cert_id
        /// U8 凭证传递-----AGENCY_STR_CODE--单位支出结构编码 主要到库中查看值
        /// </summary>SQL里的yskmdm--功能科目代码,jflxdm-经济科目代码,czyID--用户ID,czyname--用户名称
        /// <returns>pdqj- 当前日期yyyyMMdd,pdqj1--支付日期yyyyMMdd,pdh -支付凭证编码,qr_rq-支付/清算日期yyyyMM,EXP_FUNC_CODE--功能科目，
        /// PAY_AMT--支付金额,USE_DES--备注，DEP_BGT_ECO_CODE--经济科目代码，PAY_CERT_ID--支付ID,AGENCY_STR_CODE--单位支出结构编码,
        /// FUND_TYPE_CODE--资金性质编码,PAYEE_ACCT_NAME----收款人名称 </returns>
        public static async Task<int> Insertpz(string gsdm, string pdqj, string kjnd, string pdh, string pdqj1, string qr_rq, string xmdm, string xmmc,
            string czyID, string czyname, string PAY_CERT_ID, string EXP_FUNC_CODE, string PAY_AMT, string DEP_BGT_ECO_CODE, string USE_DES,
            string AGENCY_STR_CODE, string FUND_TYPE_CODE, string PAYEE_ACCT_NAME)
        {
            string AGENCY_STR_CODE1 = "";
            // 将一体化中的参数 转化为 U8中的参数 （单位支出结构编码）
            if (AGENCY_STR_CODE == "001") //基本支出
            {
                AGENCY_STR_CODE1 = "01";
            }
            if (AGENCY_STR_CODE == "002")// 项目支出
            {
                AGENCY_STR_CODE1 = "02";
            }
            // 将一体化中的资金性质编码（FUND_TYPE_CODE）转为U8 可用的
            string FUND_TYPE_CODE1 = "";
            if (FUND_TYPE_CODE == "121")//政府性基金预算资金
            {
                FUND_TYPE_CODE1 = "12";
            }
            if (FUND_TYPE_CODE == "111")//一般公共预算资金
            {
                FUND_TYPE_CODE1 = "11";
            }
            if (FUND_TYPE_CODE == "22")//社保资金
            {
                FUND_TYPE_CODE1 = "14";
            }
            if (FUND_TYPE_CODE == "21")//财政专户
            {
                FUND_TYPE_CODE1 = "15";
            }
            if (FUND_TYPE_CODE == "112")//一般债券
            {
                FUND_TYPE_CODE1 = "11";
            }

            //1.1判断项目是否存在,添加项目
            string sql = " declare @rq nvarchar(50);declare  @idpzh varchar(50);declare @pdqj nvarchar(50); declare @gsdm nvarchar(8);set @gsdm='" + gsdm + "'; set @pdqj=LEFT('" + qr_rq + "',6);set @idpzh=NEWID();set @rq=CONVERT(varchar(100), GETDATE(), 112);";
            sql += "  begin transaction  if '" + gsdm + "' not like '18%' begin  begin transaction ";
            sql += "if ( select COUNT(*) from gl_xmzl where  GSDM='" + gsdm + "' and  KJND='" + kjnd + "'  and XMDM=right('000000000'+convert(varchar,'" + xmdm + "'),10))<1";
            sql += " insert into gl_xmzl (gsdm,KJND,XMDM,XMMC,ZJM,KSRQ,JSRQ,BMDM,BM,REN,ZY,SYZT,JLR_ID,JL_RQ,XGR_ID,XG_RQ,SJLY,SFMX,PROJOBJID ,ZDXMBZ,KZFS,ISJBZC ,LXND,ZJLY,XMFL,SFJT,REN2,REN3,YSJE,GLH)";
            sql += "  select '" + gsdm + "' as gsdm,'" + kjnd + "' as kjnd,right('000000000'+convert(varchar,'" + xmdm + "'),10) as xmdm,'" + xmmc + "' as xmmc,'' as zjm,";
            sql += " @rq as ksrq,@rq as jsrq,'' as bmdm,'' as bm,'' as ren,'' as zy,'0' as syzt,";
            sql += " '1' as jlr_id,@rq as jl_rq,'1' as xgr_id,@rq as xg_rq,'M,S,|M,S,' as SJLY,'1' as sfmx,'' as projobjid,";
            sql += " '0' as zdxmbz,'0' as kzfs,'0' as isjbzc,'" + kjnd + "' as lxnd,'' as zjly,'' as xmfl,'0' as sfjt,'' as ren2,";
            sql += "  '' as ren3,null as ysje,'' as glh";
            sql += " commit transaction";
            //2.插入收入凭证

            //2.1 插入收入凭证目录
            string sql1 = "   begin transaction  ";
            sql1 += " insert into gl_pzml";
            sql1 += "  select '" + gsdm + "' as gsdm,'" + gsdm + "' as zth,LEFT('" + qr_rq + "',6) as kjqj,'' as pzly,'' as KJTXDM,'记账' as PZXLDM,";
            sql1 += "  isnull((select Max(convert(int,pzh)) from gl_pzml where kjqj=LEFT('" + qr_rq + "',6) and gsdm='" + gsdm + "' ),'0')+1  as pzh,";
            sql1 += "  '" + qr_rq + "' as pzrq,'0' as fjzs,'" + czyID + "' as srID,'" + czyname + "' as sr,'-1' as shID,'' as sh,'' as jzr,'-1' as jzrID,'' as jzr,";
            sql1 += "  '" + qr_rq + "' as srrq,'' as shrq,'' as jzrq,'' as pzhzkmdy,'' as pzhzbz,'1' as zt,'" + USE_DES + "' as pzzy,'" + PAY_AMT + "' as pzje,'' as CN,";
            sql1 += "  '' as BZ,(select cwzg from gl_ztcs where kjnd='" + kjnd + "' and ztbh='" + gsdm + "') as kjzg,@idpzh as idpzh,'0' as dyzt,'' as qzrq,'-1' as GDID,";
            sql1 += "  '' as GDName,'' as GDRQSJ,'0' as GDCS,'0' as GDBZ,'' as PZSBM,'0' as STAMP,'0' as yspzzs,";
            sql1 += "  case when '" + EXP_FUNC_CODE + "' <>'27005' then '" + PAY_AMT + "' else '0' end as YSPZJE";

            //2.2插入凭证内容
            //2.2.1财务会计借方    -----------少判断不等于往来款-------
            string sql2 = " if ('" + EXP_FUNC_CODE + "'<>'27005') begin";
            sql2 += "   insert into gl_pznr";
            sql2 += " select '" + gsdm + "' as gsdm,'" + gsdm + "' as zth,LEFT('" + qr_rq + "',6) as kjqj,'' as pzly,'01' as KJTXDM,'记账' as PZXLDM,";
            sql2 += " isnull((select Max(convert(int,pzh))  from gl_pzml where kjqj=LEFT('" + qr_rq + "',6) and gsdm='" + gsdm + "' ),'0') as pzh,";
            sql2 += " '1' as flh,'" + USE_DES + "' as pzzy, case when left('" + DEP_BGT_ECO_CODE + "',3)='301' then '500101'";
            sql2 += " when left('" + DEP_BGT_ECO_CODE + "',3)='302' then '500102' when left('" + DEP_BGT_ECO_CODE + "',3)='303' then '500103'";
            sql2 += " when left('" + DEP_BGT_ECO_CODE + "',3)='311' then '500104' when left('" + DEP_BGT_ECO_CODE + "',3)='312' then '500104'";
            sql2 += " else '' end as kmdm,'' as wbdm,'1' as hl,'借' as jdbz,'0' as wbje,'" + PAY_AMT + "' as je,";
            sql2 += " '' as spz,'' as wldrq,'0' as sl,'0' as dj,'' as bmdm,'' as wldm,'' as xmdm,'' as fzsm1,'" + PAY_CERT_ID + "' as fzsm2,'' as fzsm3,'' as fzsm4,";
            sql2 += " '' as fzsm5,'' as fzsm6,'' as fzsm7,'' as fzsm8,'' as fzsm9,'0' as cess,'' as fplx,'' as fprq,'0' as fdhfw1,";
            sql2 += "  '0' as fdhfw2,'' as jsfs,'' as zydm,'' as fzdm4,'' as fzdm5,'' as fzdm6,'' as fzdm7,'' as fzdm8,'' as fzdm9,'' as fzdm10,'' as fzdm11,";
            sql2 += " '' as fzdm12,'' as fzdm13,'' as fzdm14,'' as fzdm15,'' as fzdm16,'' as fzdm17,'' as fzdm18,'' as fzdm19,'' as fzdm20,";
            sql2 += " '' as fzdm21,'' as fzdm22,'' as fzdm23,'' as fzdm24,'' as fzdm25,'' as fzdm26,'' as fzdm27,'' as fzdm28,'' as fzdm29,'' as fzdm30,";
            sql2 += "  newid() as idpznr,'' as wlh,@idpzh as idpzh,'0' as zbid,'0' as edid,'0' as jfsqid,'0' as bxdid,'' as htid,";
            sql2 += " '0' as zckpbh,'' as skrlx,'' as skrmc,'' as skrzh,'' as skryhdm,'' as skryhhh,'' as skryhmc,'' as  sflx,";
            sql2 += " '0' as djbh,'0' as hzflh,'' as yszccy,'' as cyxfx,'' as dqrq,'' as GLIDPZNR,'' as xsflh";

            //2.2.2财务会计贷方
            string sql3 = " union  select '" + gsdm + "' as gsdm,'" + gsdm + "' as zth,LEFT('" + qr_rq + "',6) as kjqj,'' as pzly,'01' as KJTXDM,'记账' as PZXLDM,";
            sql3 += " isnull((select Max(convert(int,pzh))  from gl_pzml where kjqj=LEFT('" + qr_rq + "',6) and gsdm='" + gsdm + "' ),'0') as pzh,";
            sql3 += " '2' as flh,'" + USE_DES + "' as pzzy,'4001' as kmdm,'' as wbdm,'1' as hl,'贷' as jdbz,'0' as wbje,'" + PAY_AMT + "' as je,'' as spz,";
            sql3 += "  '' as wldrq,'0' as sl,'0' as dj,'' as bmdm,'' as wldm,'' as xmdm,'' as fzsm1,'" + PAY_CERT_ID + "' as fzsm2,'' as fzsm3,";
            sql3 += " '' as fzsm4,'' as fzsm5,'' as fzsm6,'' as fzsm7,'' as fzsm8,'' as fzsm9,'0' as cess,'' as fplx,";
            sql3 += " '' as fprq,'0' as fdhfw1,'0' as fdhfw2,'' as jsfs,'' as zydm,'' as fzdm4,'' as fzdm5,";
            sql3 += " '' as fzdm6,'' as fzdm7,'' as fzdm8,'' as fzdm9,'' as fzdm10,'' as fzdm11,'' as fzdm12,";
            sql3 += " '' as fzdm13,'' as fzdm14,'' as fzdm15,'' as fzdm16,'' as fzdm17,'' as fzdm18,'' as fzdm19,";
            sql3 += " '' as fzdm20,'' as fzdm21,'' as fzdm22,'' as fzdm23,'' as fzdm24,'' as fzdm25,'' as fzdm26,";
            sql3 += " '' as fzdm27,'' as fzdm28,'' as fzdm29,'' as fzdm30,newid() as idpznr,'' as wlh,@idpzh as idpzh,";
            sql3 += " '0' as zbid,'0' as edid,'0' as jfsqid,'0' as bxdid,'' as htid,'0' as zckpbh,'' as skrlx,'' as skrmc,";
            sql3 += " '' as skrzh,'' as skryhdm,'' as skryhhh,'' as skryhmc,'' as  sflx,'0' as djbh,'0' as hzflh,'' as yszccy,";
            sql3 += " '' as cyxfx,'' as dqrq,'' as GLIDPZNR,'' as xsflh";

            //2.2.3预算会计借方 
            string sql4 = " union select '" + gsdm + "' as gsdm,'" + gsdm + "' as zth,LEFT('" + qr_rq + "',6) as kjqj,'' as pzly,'02' as KJTXDM,'记账' as PZXLDM,";
            sql4 += "  isnull((select Max(convert(int,pzh))  from gl_pzml where kjqj=LEFT('" + qr_rq + "',6) and gsdm='" + gsdm + "' ),'0') as pzh,";
            sql4 += " '3' as flh,'" + USE_DES + "' as pzzy,case when (select hymc from PUBgszl where gsdm='" + gsdm + "' and kjnd='" + kjnd + "') = '行政单位' ";
            sql4 += " and  LEFT('" + DEP_BGT_ECO_CODE + "',3) in ('301','303','310') and '" + AGENCY_STR_CODE1 + "'='01' then '7101010101'";
            sql4 += " when (select hymc from PUBgszl where gsdm='" + gsdm + "' and kjnd='" + kjnd + "') = '行政单位' and LEFT('" + DEP_BGT_ECO_CODE + "',3) not in('301','303')";
            sql4 += " and '" + AGENCY_STR_CODE1 + "'='01' then '7101010102' when (select hymc from PUBgszl where gsdm='" + gsdm + "' and kjnd='" + kjnd + "') = '行政单位'";
            sql4 += " and '" + AGENCY_STR_CODE1 + "' in('02','03') then '71010102' when (select hymc from PUBgszl where gsdm='" + gsdm + "' and kjnd='" + kjnd + "') = '事业单位'";
            sql4 += " and LEFT('" + DEP_BGT_ECO_CODE + "',3) in('301','303') and '" + AGENCY_STR_CODE1 + "'='01'then '720101010101'";
            sql4 += "  when (select hymc from PUBgszl where gsdm='" + gsdm + "' and kjnd='" + kjnd + "') = '事业单位' and LEFT('" + DEP_BGT_ECO_CODE + "',3)";
            sql4 += "  not in('301','303') and '" + AGENCY_STR_CODE1 + "'='01' then '720101010102' else '7201010102' end as kmdm,";
            sql4 += " '' as wbdm,'1' as hl,'借' as jdbz,'0' as wbje,'" + PAY_AMT + "' as je,'' as spz,'' as wldrq,'0' as sl,'0' as dj,'' as bmdm,";
            sql4 += " '' as wldm,right('000000000'+convert(varchar,'" + xmdm + "'),10) as xmdm,'" + PAYEE_ACCT_NAME + "' as fzsm1,'" + PAY_CERT_ID + "' as fzsm2,";
            sql4 += "  '' as fzsm3,'' as fzsm4,'' as fzsm5,'' as fzsm6,'' as fzsm7,'' as fzsm8,'' as fzsm9,'0' as cess,";
            sql4 += " '' as fplx,'' as fprq,'0' as fdhfw1,'0' as fdhfw2,'' as jsfs,'' as zydm,'" + EXP_FUNC_CODE + "' as fzdm4,'" + DEP_BGT_ECO_CODE + "' as fzdm5,";
            sql4 += " '" + FUND_TYPE_CODE1 + "' as fzdm6,'' as fzdm7,'' as fzdm8,'' as fzdm9,'' as fzdm10,'' as fzdm11,'' as fzdm12,'' as fzdm13,";
            sql4 += " '' as fzdm14,'' as fzdm15,'' as fzdm16,'' as fzdm17,'' as fzdm18,'' as fzdm19,'' as fzdm20,'' as fzdm21,";
            sql4 += " '' as fzdm22,'' as fzdm23,'' as fzdm24,'' as fzdm25,'' as fzdm26,'' as fzdm27,'' as fzdm28,'' as fzdm29,'' as fzdm30,newid() as idpznr,";
            sql4 += " '' as wlh,@idpzh as idpzh,'0' as zbid,'0' as edid,'0' as jfsqid,'0' as bxdid,'' as htid,'0' as zckpbh,";
            sql4 += " '' as skrlx,'' as skrmc,'' as skrzh,'' as skryhdm,'' as skryhhh,'' as skryhmc,'' as sflx,";
            sql4 += "  '0' as djbh,'0' as hzflh,'' as yszccy,'' as cyxfx,'' as dqrq,'' as GLIDPZNR,'' as xsflh ";

            //2.2.4预算会计贷方  
            string sql5 = "  union select '" + gsdm + "' as gsdm,'" + gsdm + "' as zth,LEFT('" + qr_rq + "',6) as kjqj,'' as pzly,'02' as KJTXDM,'记账' as PZXLDM,";
            sql5 += " isnull((select Max(convert(int,pzh))  from gl_pzml where kjqj=LEFT('" + qr_rq + "',6) and gsdm='" + gsdm + "' ),'0') as pzh,";
            sql5 += " '4' as flh,'" + USE_DES + "' as pzzy,case when LEFT('" + DEP_BGT_ECO_CODE + "',3)  in('301','303') and '" + AGENCY_STR_CODE1 + "'='01' and '" + FUND_TYPE_CODE1 + "'<>'15' then '60010101'";
            sql5 += " when LEFT('" + DEP_BGT_ECO_CODE + "',3) not in('301','303') and '" + AGENCY_STR_CODE1 + "'='01' and '" + FUND_TYPE_CODE1 + "'<>'15' then '60010102'";
            sql5 += " when '" + AGENCY_STR_CODE1 + "'<>'01' and '" + FUND_TYPE_CODE1 + "'<>'15' then '600102'";
            sql5 += " when (select hymc from PUBgszl where gsdm='" + gsdm + "' and kjnd='" + kjnd + "') = '事业单位' and LEFT('" + DEP_BGT_ECO_CODE + "',3) in('301','303') and '" + AGENCY_STR_CODE1 + "'='01' and '" + FUND_TYPE_CODE1 + "'='15' then '6101990101'";
            sql5 += " when (select hymc from PUBgszl where gsdm='" + gsdm + "' and kjnd='" + kjnd + "') = '事业单位' and  LEFT('" + DEP_BGT_ECO_CODE + "',3) not in('301','303') and '" + AGENCY_STR_CODE1 + "'='01' and '" + FUND_TYPE_CODE1 + "'='15' then '6101990102' ";
            sql5 += " when (select hymc from PUBgszl where gsdm='" + gsdm + "' and kjnd='" + kjnd + "') = '事业单位' and '" + AGENCY_STR_CODE1 + "'<>'01' and '" + FUND_TYPE_CODE1 + "'='15' then '61019902'";
            sql5 += " when (select hymc from PUBgszl where gsdm='" + gsdm + "' and kjnd='" + kjnd + "') = '行政单位' and   '" + AGENCY_STR_CODE1 + "'='01' and '" + FUND_TYPE_CODE1 + "'='15' then '66090109'";
            sql5 += " when (select hymc from PUBgszl where gsdm='" + gsdm + "' and kjnd='" + kjnd + "') = '行政单位' and '" + AGENCY_STR_CODE1 + "'<>'01' and '" + FUND_TYPE_CODE1 + "'='15' then '66090209'";
            sql5 += " else '600102' end as kmdm,'' as wbdm,'1' as hl,'贷' as jdbz,'0' as wbje,'" + PAY_AMT + "' as je,'' as spz,'' as wldrq,'0' as sl,'0' as dj,'' as bmdm,'' as wldm,";
            sql5 += " right('000000000'+convert(varchar,'" + xmdm + "'),10) as xmdm,'" + PAYEE_ACCT_NAME + "' as fzsm1,'" + PAY_CERT_ID + "' as fzsm2,'' as fzsm3,'' as fzsm4,'' as fzsm5,'' as fzsm6,'' as fzsm7,'' as fzsm8,'' as fzsm9,'0' as cess,'' as fplx,";
            sql5 += " '' as fprq,'0' as fdhfw1,'0' as fdhfw2,'' as jsfs,'' as zydm,'" + EXP_FUNC_CODE + "' as fzdm4,'' as fzdm5,";
            sql5 += " '" + FUND_TYPE_CODE1 + "' as fzdm6,'' as fzdm7,'' as fzdm8,'' as fzdm9,'' as fzdm10,'' as fzdm11,'' as fzdm12,";
            sql5 += "  '' as fzdm13,'' as fzdm14,'' as fzdm15,'' as fzdm16,'' as fzdm17,'' as fzdm18,'' as fzdm19,'' as fzdm20,";
            sql5 += " '' as fzdm21,'' as fzdm22,'' as fzdm23,'' as fzdm24,'' as fzdm25,'' as fzdm26, '' as fzdm27,'' as fzdm28,'' as fzdm29,'' as fzdm30,newid() as idpznr,'' as wlh,";
            sql5 += "  @idpzh as idpzh,'0' as zbid,'0' as edid,'0' as jfsqid,'0' as bxdid,'' as htid,'0' as zckpbh,'' as skrlx,";
            sql5 += " '' as skrmc,'' as skrzh,'' as skryhdm,'' as skryhhh,'' as skryhmc,'' as sflx,'0' as djbh,'0' as hzflh,";
            sql5 += " '' as yszccy,'' as cyxfx,'' as dqrq,'' as GLIDPZNR,'' as xsflh  end";

            //
            string sql6 = " if ('" + EXP_FUNC_CODE + "')='27005' begin";
            sql6 += "   insert into gl_pznr(gsdm, ZTH, kjqj, pzly, KJTXDM, PZLXDM, pzh, flh, zy, kmdm, wbdm, hl, jdbz, wbje, je, spz, wldrq, sl, dj, bmdm, wldm, xmdm, fzsm1, fzsm2, fzsm3, fzsm4, fzsm5, fzsm6, fzsm7, fzsm8, fzsm9,";
            sql6 += " cess, fplx, fprq, fphfw1, fphfw2, jsfs, zydm, fzdm4, fzdm5, fzdm6, fzdm7, fzdm8, fzdm9, fzdm10, fzdm11, fzdm12, fzdm13, fzdm14, fzdm15, fzdm16, fzdm17, fzdm18, fzdm19, fzdm20, fzdm21,";
            sql6 += " fzdm22, fzdm23, fzdm24, fzdm25, fzdm26, fzdm27, fzdm28, fzdm29, fzdm30, IDPZNR, wlh, IDPZH, ZBID, EDID, JFSQID, BXDID, HTID, ZCKPBH, SKRLX, SKRMC, SKRZH, SKRYHDM, SKRYHHH,";
            sql6 += " SKRYHMC, SFLX, DJBH, HZFLH, YSZCCY, CYXFX, DQRQ, GLIDPZNR, XSFLH)";
            sql6 += "  select '" + gsdm + "' as gsdm,'" + gsdm + "' as zth,LEFT('" + qr_rq + "',6) as kjqj,'' as pzly,'01' as KJTXDM,'记账' as PZXLDM,";
            sql6 += "  isnull((select Max(convert(int,pzh))  from gl_pzml where kjqj=LEFT('" + qr_rq + "',6) and gsdm='" + gsdm + "' ),'0') as pzh,";
            sql6 += " '1' as flh,'" + USE_DES + "' as pzzy, '2307' as kmdm,'' as wbdm,'1' as hl,'借' as jdbz,'0' as wbje,'" + PAY_AMT + "' as je,'' as spz,'' as wldrq,'0' as sl,'0' as dj,";
            sql6 += " '' as bmdm,'' as wldm,'' as xmdm,'' as fzsm1,'" + PAY_CERT_ID + "' as fzsm2,'' as fzsm3,'' as fzsm4,'' as fzsm5,";
            sql6 += " '' as fzsm6,'' as fzsm7,'' as fzsm8,'' as fzsm9,'0' as cess,'' as fplx,'' as fprq,'0' as fdhfw1,";
            sql6 += "  '0' as fdhfw2,'' as jsfs,'' as zydm,'' as fzdm4,'' as fzdm5,";
            sql6 += " '' as fzdm6,'' as fzdm7,'' as fzdm8,'' as fzdm9,'' as fzdm10,'' as fzdm11,'' as fzdm12,'' as fzdm13,'' as fzdm14,";
            sql6 += " '' as fzdm15,'' as fzdm16,'' as fzdm17,'' as fzdm18,'' as fzdm19,'' as fzdm20,'' as fzdm21,'' as fzdm22,'' as fzdm23,'' as fzdm24,'' as fzdm25,'' as fzdm26, ";
            sql6 += "  '' as fzdm27,'' as fzdm28,'' as fzdm29,'' as fzdm30,newid() as idpznr,'' as wlh,@idpzh as idpzh,'0' as zbid,'0' as edid,";
            sql6 += " '0' as jfsqid,'0' as bxdid,'' as htid,'0' as zckpbh,'' as skrlx,'' as skrmc,'' as skrzh,'' as skryhdm,'' as skryhhh,";
            sql6 += " '' as skryhmc,'' as  sflx,'0' as djbh,'0' as hzflh,'' as yszccy,'' as cyxfx,'' as dqrq,'' as GLIDPZNR,'' as xsflh ";

            //2.2.2财务会计贷方 
            string sql7 = " union";
            sql7 += " select '" + gsdm + "' as gsdm,'" + gsdm + "' as zth,LEFT('" + qr_rq + "',6) as kjqj,'' as pzly,'01' as KJTXDM,'记账' as PZXLDM,";
            sql7 += " isnull((select Max(convert(int,pzh))  from gl_pzml where kjqj=LEFT('" + qr_rq + "',6) and gsdm='" + gsdm + "' ),'0') as pzh,";
            sql7 += "  '2' as flh,'" + USE_DES + "' as pzzy,'2307' as kmdm,'' as wbdm,'1' as hl,'贷' as jdbz,'0' as wbje,'" + PAY_AMT + "' as je,'' as spz,'' as wldrq,'0' as sl,'0' as dj,'' as bmdm,";
            sql7 += "  '' as wldm,'' as xmdm,'' as fzsm1,'" + PAY_CERT_ID + "' as fzsm2,'' as fzsm3,'' as fzsm4,'' as fzsm5,'' as fzsm6,'' as fzsm7,'' as fzsm8,";
            sql7 += " '' as fzsm9,'0' as cess,'' as fplx,'' as fprq,'0' as fdhfw1,'0' as fdhfw2,'' as jsfs,'' as zydm,'' as fzdm4,'' as fzdm5,";
            sql7 += " '' as fzdm6,'' as fzdm7,'' as fzdm8,'' as fzdm9,'' as fzdm10,'' as fzdm11,'' as fzdm12,'' as fzdm13,'' as fzdm14,";
            sql7 += " '' as fzdm15,'' as fzdm16,'' as fzdm17,'' as fzdm18,'' as fzdm19,'' as fzdm20,'' as fzdm21,'' as fzdm22,'' as fzdm23,";
            sql7 += " '' as fzdm24,'' as fzdm25,'' as fzdm26,'' as fzdm27,'' as fzdm28,'' as fzdm29,'' as fzdm30,newid() as idpznr,'' as wlh,@idpzh as idpzh,";
            sql7 += " '0' as zbid,'0' as edid,'0' as jfsqid,'0' as bxdid,'' as htid,'0' as zckpbh,'' as skrlx,'' as skrmc,";
            sql7 += " '' as skrzh,'' as skryhdm,'' as skryhhh,'' as skryhmc,'' as sflx,'0' as djbh,'0' as hzflh,'' as yszccy,'' as cyxfx,'' as dqrq,'' as GLIDPZNR,'' as xsflh end";

            ///3插入凭证关联关系表
            string sql8 = " insert into GL_PZGROUP";

            sql8 += " select '" + gsdm + "' as gsdm,'" + gsdm + "' as zth,LEFT('" + qr_rq + "',6) as kjqj,@pdqj+'" + pdh + "' as cwidpzh,'' as ysidpzh ";
            //4反写当前库的状态

            //string sql9 = " update zb_zfpzml_y set JZR_ID1='" + czyID + "',JZR1='" + czyname + "',";
            //sql9 += " IDPZH1=isnull((select Max(convert(int,pzh))  from gl_pzml where kjqj=LEFT('" + qr_rq + "',6) and gsdm='" + gsdm + "' ),'0')";
            //sql9 += "where kjnd='" + kjnd + "' and pdqj='" + pdqj1 + "' and pdh='" + pdh + "'   commit transaction end";
            string sql9 = "  commit transaction end";

            string sql10 = "  if @gsdm like '18%' ";
            sql10 += " begin  begin transaction";
            sql10 += " insert into gl_pzml(  gsdm, ZTH, kjqj, pzly, KJTXDM, PZLXDM, pzh, pzrq, fjzs, srID, sr, shID, sh, jsr, jzrID, jzr, srrq, shrq, jzrq, pzhzkmdy, pzhzbz, zt, pzzy, pzje, CN, BZ, kjzg, idpzh, dyzt, QZRQ, GDID, GDName,GDRQSJ, GDCS, GDBZ, PZSBM, STAMP, yspzzs, YSPZJE) ";
            sql10 += " select '" + gsdm + "' as gsdm,'" + gsdm + "' as zth,LEFT('" + qr_rq + "',6) as kjqj,'' as pzly,'' as KJTXDM,'记账' as PZXLDM,";
            sql10 += " (select '记账'+right('      '+convert(varchar(10),isnull(Max(convert(int,substring(pzh,6,5))),0)+1),6) from gl_pzml where kjqj=LEFT('" + qr_rq + "',6) and gsdm='" + gsdm + "' ) as pzh,";
            sql10 += " '" + qr_rq + "' as pzrq,'0' as fjzs,'" + czyID + "' as srID,'" + czyname + "' as sr,'-1' as shID,'' as sh,'' as jzr,'-1' as jzrID,'' as jzr,";
            sql10 += " '" + qr_rq + "' as srrq,'' as shrq,'' as jzrq,'' as pzhzkmdy,'' as pzhzbz,'1' as zt,'" + USE_DES + "' as pzzy,'" + PAY_AMT + "' as pzje,'' as CN,";
            sql10 += " '' as BZ,(select cwzg from gl_ztcs where kjnd='" + kjnd + "' and ztbh='" + gsdm + "') as kjzg,@idpzh as idpzh,'0' as dyzt,'' as qzrq,";
            sql10 += " '-1' as GDID,'' as GDName,'' as GDRQSJ,'0' as GDCS,'0' as GDBZ,'' as PZSBM,'0' as STAMP,'0' as yspzzs,'0' as YSPZJE";

            string sql11 = " insert into gl_pznr(gsdm, ZTH, kjqj, pzly, KJTXDM, PZLXDM, pzh, flh, zy, kmdm, wbdm, hl, jdbz, wbje, je, spz, wldrq, sl,";
            sql11 += " dj, bmdm, wldm, xmdm, fzsm1, fzsm2, fzsm3, fzsm4, fzsm5, fzsm6, fzsm7, fzsm8, fzsm9,";
            sql11 += " cess, fplx, fprq, fphfw1, fphfw2, jsfs, zydm, fzdm4, fzdm5, fzdm6, fzdm7, fzdm8, fzdm9, fzdm10,";
            sql11 += " fzdm11, fzdm12, fzdm13, fzdm14, fzdm15, fzdm16, fzdm17, fzdm18, fzdm19, fzdm20, fzdm21,";
            sql11 += " fzdm22, fzdm23, fzdm24, fzdm25, fzdm26, fzdm27, fzdm28, fzdm29, fzdm30, IDPZNR, wlh, IDPZH, ZBID, EDID, JFSQID,";
            sql11 += " BXDID, HTID, ZCKPBH, SKRLX, SKRMC, SKRZH, SKRYHDM, SKRYHHH,   SKRYHMC, SFLX, DJBH, HZFLH, YSZCCY, CYXFX, DQRQ, GLIDPZNR, XSFLH)";
            sql11 += " select '" + gsdm + "' as gsdm,'" + gsdm + "' as zth,LEFT('" + qr_rq + "',6) as kjqj,'' as pzly,'01' as KJTXDM,'记账' as PZXLDM,";
            sql11 += " (select '记账'+right('      '+convert(varchar(10),isnull(Max(convert(int,substring(pzh,6,5))),0)),6) from gl_pzml where";
            sql11 += " kjqj=LEFT('" + qr_rq + "',6) and gsdm='" + gsdm + "') as pzh,'1' as flh,'" + USE_DES + "' as pzzy,'50010101' as kmdm,'' as wbdm,'1' as hl,'借' as jdbz,";
            sql11 += " '0' as wbje,'" + PAY_AMT + "' as je,'' as spz,'' as wldrq,'0' as sl,'0' as dj,'' as bmdm,'' as wldm,'' as xmdm,'' as fzsm1,'" + PAY_CERT_ID + "' as fzsm2,";
            sql11 += " '' as fzsm3,'' as fzsm4,'' as fzsm5,'' as fzsm6,'' as fzsm7,'' as fzsm8,'' as fzsm9,'0' as cess,'' as fplx,";
            sql11 += " '' as fprq,'0' as fdhfw1,'0' as fdhfw2,'' as jsfs,'' as zydm,'" + EXP_FUNC_CODE + "' as fzdm4,'" + DEP_BGT_ECO_CODE + "' as fzdm5, ";
            sql11 += "  '' as fzdm6,'' as fzdm7,'' as fzdm8,'' as fzdm9,'' as fzdm10,'' as fzdm11,'' as fzdm12,'' as fzdm13,'' as fzdm14,";
            sql11 += "  '' as fzdm15,'' as fzdm16,'' as fzdm17,'' as fzdm18,'' as fzdm19,'' as fzdm20,'' as fzdm21,'' as fzdm22,";
            sql11 += " '' as fzdm23,'' as fzdm24,'' as fzdm25,'' as fzdm26,'' as fzdm27,'' as fzdm28,'' as fzdm29,'' as fzdm30,";
            sql11 += "  newid() as idpznr,'' as wlh,@idpzh as idpzh,'0' as zbid,'0' as edid,'0' as jfsqid,'0' as bxdid,'' as htid,";
            sql11 += " '0' as zckpbh,'' as skrlx,'' as skrmc,'' as skrzh,'' as skryhdm,'' as skryhhh,'' as skryhmc,'' as  sflx,";
            sql11 += " '0' as djbh,'0' as hzflh,'' as yszccy,'' as cyxfx,'' as dqrq,'' as GLIDPZNR,'' as xsflh  union";

            //--2.2.2财务会计贷方
            string sql12 = " select '" + gsdm + "' as gsdm,'" + gsdm + "' as zth,LEFT('" + qr_rq + "',6) as kjqj,'' as pzly,'01' as KJTXDM,'记账' as PZXLDM,";
            sql12 += " (select '记账'+right('      '+convert(varchar(10),isnull(Max(convert(int,substring(pzh,6,5))),0)),6) from gl_pzml";
            sql12 += " where kjqj=LEFT('" + qr_rq + "',6) and gsdm='" + gsdm + "') as pzh,'2' as flh,'" + USE_DES + "' as pzzy,'400101' as kmdm,'' as wbdm,'1' as hl,";
            sql12 += "  '贷' as jdbz,'0' as wbje,'" + PAY_AMT + "' as je,'' as spz,'' as wldrq,'0' as sl,'0' as dj,'' as bmdm,";
            sql12 += " '' as wldm,'' as xmdm,'' as fzsm1,'" + PAY_CERT_ID + "' as fzsm2,'' as fzsm3,'' as fzsm4,'' as fzsm5,'' as fzsm6,'' as fzsm7,";
            sql12 += " '' as fzsm8,'' as fzsm9,'0' as cess,'' as fplx,'' as fprq,'0' as fdhfw1,'0' as fdhfw2,'' as jsfs,'' as zydm,";
            sql12 += "  '' as fzdm4,'' as fzdm5,'' as fzdm6,'' as fzdm7,'' as fzdm8,'' as fzdm9,'' as fzdm10,'' as fzdm11,'' as fzdm12,";
            sql12 += "  '' as fzdm13,'' as fzdm14,'' as fzdm15,'' as fzdm16,'' as fzdm17,'' as fzdm18,'' as fzdm19,'' as fzdm20,'' as fzdm21,";
            sql12 += " '' as fzdm22,'' as fzdm23,'' as fzdm24,'' as fzdm25,'' as fzdm26, '' as fzdm27,'' as fzdm28,'' as fzdm29,'' as fzdm30,";
            sql12 += " newid() as idpznr,'' as wlh,@idpzh as idpzh,'0' as zbid,'0' as edid,'0' as jfsqid,'0' as bxdid,'' as htid,";
            sql12 += " '0' as zckpbh,'' as skrlx,'' as skrmc,'' as skrzh,'' as skryhdm,'' as skryhhh,'' as skryhmc,'' as sflx,'0' as djbh,";
            sql12 += " '0' as hzflh,'' as yszccy,'' as cyxfx,'' as dqrq,'' as GLIDPZNR,'' as xsflh ";

            //--3插入凭证关联关系表 


            string sql13 = " insert into GL_PZGROUP  ";
            sql13 += " select '" + gsdm + "' as gsdm,'" + gsdm + "' as zth,LEFT('" + qr_rq + "',6) as kjqj,@pdqj+substring('" + pdh + "',5,6) as cwidpzh,'' as ysidpzh ";
            //4反写当前库的状态
            //string sql14 = " update zb_zfpzml_y set JZR_ID1='" + czyID + "',JZR1='" + czyname + "',";
            //sql14 += " IDPZH1=isnull((select Max(convert(int,substring(pzh,5,6)))  from gl_pzml where kjqj=LEFT('" + qr_rq + "',6) and gsdm='" + gsdm + "' ),'0')";
            //sql14 += " where kjnd='" + kjnd + "' and pdqj='" + pdqj1 + "' and pdh='" + pdh + "' commit transaction  end  commit transaction ";

            string sql14 = " commit transaction  end  commit transaction";



            string sql0 = sql + sql1 + sql2 + sql3 + sql4 + sql5 + sql6 + sql7 + sql8 + sql9 + sql10 + sql11 + sql12 + sql13 + sql14;
            return await new RepositoryFactory().XCQRepository().ExecuteSql(sql0);

        }
        /// <summary>
        /// 省厅回流库 传递凭证标记
        /// </summary>
        public static async Task<int> insertPAYCERTID(string PAY_CERT_ID)
        {
            string sql = "  insert into pay_pz (PAY_CERT_ID) values('" + PAY_CERT_ID + "')  ";
            return await new RepositoryFactory().XCQOrcaleRepository().ExecuteSql(sql);
        }


    }
}
